﻿using System;
using System.Data;
using System.Globalization;
using System.Collections;
using System.Collections.Generic;

using BizElements.Core.ProviderComponents;

namespace BizElements.Core
{
    /// <summary>
    /// Executes SQL commands.
    /// </summary>
    public static class DbUtil
    {
        #region Helper private methods.

        private static bool EnsureOpenConnection(IConnectionProvider conn)
        {
            bool connIsOpennedLocally = !conn.IsOpen;
            if (connIsOpennedLocally)
                conn.OpenConnection();
            return connIsOpennedLocally;
        }

        private static void AssignExistingPendingTransactionToCommand(IConnectionProvider conn, IDbCommand cmd)
        {
            // Assign existing transaction to this command.
            if (conn.IsTransactionPending)
                cmd.Transaction = conn.Transaction;
        } 

        private static void PopulateOutputParameterValues(DbParameterCollection parameters, IDbCommand cmd)
        {
            // Fetch output values.
            for (int paramIdx = 0; paramIdx < parameters.Count; paramIdx++)
            {
                if (parameters[paramIdx].Direction != ParameterDirection.Input)
                    parameters[paramIdx].Value = ((IDataParameter)cmd.Parameters[paramIdx]).Value;
            }
        }

        private static void CloseConnectionIfLocal(IConnectionProvider conn, bool connIsOpennedLocally)
        {
            // Close connection if it was openned in this method.
            if (connIsOpennedLocally)
                conn.CloseConnection();
        }

        /// <summary>
        /// Creates DB command suitable for the given DBMS type.
        /// </summary>
        /// <param name="dbms">DBMS type (SqlServer, ODBC, OLE...).</param>
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (Text, Stored procedure).</param>
        /// <returns>DB command that works on the specified provider.</returns>
        private static IDbCommand CreateCommand(DbmsType dbms, string cmdText, DbParameterCollection parameters, CommandType cmdType)
        {
            IDbCommandBuilder builder = DbmsComponentFactory.GetComponent<IDbCommandBuilder>(dbms);
            IDbCommand cmd = builder.BuildCommand(cmdText, parameters, cmdType);
            if (Settings.DefaultCommandTimeout != null)
                cmd.CommandTimeout = Settings.DefaultCommandTimeout.Value;

            return cmd;
        }

        #endregion

        #region ExecuteQuery.

        /// <summary>
        /// Creates and executes query using the given ConnectionProvider.
        /// </summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (stored procedure or text).</param>
        /// <param name="dataTableName">Name of the datatable that will be returned. May be null.</param>
        /// <returns>DataTable containing retrieved data.</returns>
        public static DataTable ExecuteQuery(IConnectionProvider conn, string cmdText, DbParameterCollection parameters, CommandType cmdType, string dataTableName)
        {            
            IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType);
            cmd.Connection = conn.Connection;
            IDataAdapterBuilder builder = DbmsComponentFactory.GetComponent<IDataAdapterBuilder>(conn.DBMS);
            IDataAdapter adapter = builder.BuildAdapter(cmd);
            bool connIsOpennedLocally = EnsureOpenConnection(conn);
            try
            {
                AssignExistingPendingTransactionToCommand(conn, cmd);

                // Execute query.
                DataSet data = new DataSet();
                data.Locale = CultureInfo.InvariantCulture;
                adapter.Fill(data);
                PopulateOutputParameterValues(parameters, cmd);

                DataTable table = (data.Tables.Count > 0) 
                    ? data.Tables[0] 
                    : new DataTable() { Locale = CultureInfo.InvariantCulture };
                if (!string.IsNullOrEmpty(dataTableName))
                    table.TableName = dataTableName;

                return table;
            }
            finally
            {
                CloseConnectionIfLocal(conn, connIsOpennedLocally);
                cmd.Dispose();
                IDisposable disposableAdapter = adapter as IDisposable;
                if (disposableAdapter != null)
                    disposableAdapter.Dispose();
            }
        }          
        
        #endregion

        #region ExecuteMultiQuery.

        /// <summary>
        /// Creates and executes query using the given ConnectionProvider which returns multiple result-sets.
        /// </summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (stored procedure or text).</param>
        /// <returns>DataSet containg retrieved data.</returns>
        public static DataSet ExecuteMultiQuery(IConnectionProvider conn, String cmdText, DbParameterCollection parameters, CommandType cmdType)
        {            
            IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType);
            cmd.Connection = conn.Connection;
            IDataAdapterBuilder builder = DbmsComponentFactory.GetComponent<IDataAdapterBuilder>(conn.DBMS);
            IDataAdapter adapter = builder.BuildAdapter(cmd);
            bool connIsOpennedLocally = EnsureOpenConnection(conn);
            try
            {
                AssignExistingPendingTransactionToCommand(conn, cmd);

                // Execute query.
                DataSet data = new DataSet();
                data.Locale = CultureInfo.InvariantCulture;
                adapter.Fill(data);
                PopulateOutputParameterValues(parameters, cmd);
                return data;
            }
            finally
            {
                CloseConnectionIfLocal(conn, connIsOpennedLocally);
                cmd.Dispose();
                IDisposable disposableAdapter = adapter as IDisposable;
                if (disposableAdapter != null)
                    disposableAdapter.Dispose();
            }
        }

        #endregion

        #region ExecuteNonQuery.

        /// <summary>
        /// Executes non data-retrieval stament (update, insert, delete, stored procedure).
        /// </summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (stored procedure or text).</param>
        /// <returns>Number of rows affected by the command.</returns>
        public static int ExecuteNonQuery(IConnectionProvider conn, string cmdText, DbParameterCollection parameters, CommandType cmdType)
        {
            IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType);
            cmd.Connection = conn.Connection;
            bool connIsOpennedLocally = EnsureOpenConnection(conn);
            try
            {
                AssignExistingPendingTransactionToCommand(conn, cmd);

                // Execute command.
                int rowsAffected = cmd.ExecuteNonQuery();
                PopulateOutputParameterValues(parameters, cmd);
                return rowsAffected;
            }
            finally
            {
                CloseConnectionIfLocal(conn, connIsOpennedLocally);
                cmd.Dispose();
            }
        }

        #endregion

        #region ExecuteReader<T>.

        /// <summary>Executes a query and converts data to specified objects.</summary>
        /// <param name="conn">Connection provider.</param>
        /// <param name="destination">A collection to fill with objects built from data.</param>
        /// <param name="objectBuilder">Builds objects directly from retrieved data.</param>        
        /// <param name="cmdText">Command text.</param>
        /// <param name="parameters">Command parameters.</param>
        /// <param name="cmdType">Command type (stored procedure or text).</param>
        /// <remarks><para>The method uses provided converter to directly build specified object type from data contained in 
        /// <see cref="IDataReader"/> objects, thus skipping the need to create intermediate data structures such as <see cref="DataTable"/>
        /// and <see cref="DataSet"/> objects. This can increase performance and reduce memory consumption when working with very 
        /// large results sets.</para></remarks>
        internal static void ExecuteReader(IConnectionProvider conn, IList destination, IObjectReader objectBuilder, string cmdText, DbParameterCollection parameters, CommandType cmdType)
        {
            IDbCommand cmd = CreateCommand(conn.DBMS, cmdText, parameters, cmdType);
            cmd.Connection = conn.Connection;

            bool connIsOpennedLocally = EnsureOpenConnection(conn);
            try
            {
                AssignExistingPendingTransactionToCommand(conn, cmd);
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
                {
                    objectBuilder.FillCollection(reader, destination);
                    PopulateOutputParameterValues(parameters, cmd);
                }
            }
            finally
            {
                CloseConnectionIfLocal(conn, connIsOpennedLocally);
                cmd.Dispose();                
            }
        }

        #endregion        
    }   
}